using System;
using System.Text;
using System.Data;
using System.Collections;
using System.Collections.Generic;

using Framework;
using System.IO;

namespace SchemaDeploy
{
    //Table-Row Class (Customisable half)
    public partial class CBackupItem
    {
        #region Constants
        //Join Expressions
        public static string JOIN_BACKUP = string.Concat(TABLE_NAME, " INNER JOIN ", CBackup.TABLE_NAME, " ON ItemBackupId=BackupId"); //Nullable => Use LEFT OUTER JOIN

        #endregion

        #region Constructors (Public)
        //Default Connection String
        public CBackupItem() : base() {}
        public CBackupItem(int itemId) : base(itemId) {}
        
        //Alternative Connection String
        public CBackupItem(CDataSrc dataSrc) : base(dataSrc) {}
        
        public CBackupItem(CDataSrc dataSrc, int itemId) : base(dataSrc, itemId) {}
        
        //Transactional (shares an open connection)
        protected internal CBackupItem(CDataSrc dataSrc, int itemId, IDbTransaction txOrNull) : base(dataSrc, itemId, txOrNull) {}        
        #endregion

        #region Default Values
        protected override void InitValues_Custom()
        {
            //Custom default values (e.g. DateCreated column)
            //Member variables (e.g. for child collections)
        }
        #endregion
        
        #region Default Connection String
        protected override CDataSrc DefaultDataSrc()  { return CDataSrc.Default;  }
        #endregion

        #region Members
        //Foreign Keys
        [NonSerialized()] private CBackup _backup;

        //Child Collections  

        //Xml Data (as high-level objects)

        #endregion

        #region Properties - Relationships
        //Relationships - Foriegn Keys (e.g parent)
        public CBackup Backup
        {
            get
            {
                if (_backup == null)
                {
                    lock (this)
                    {
                        if (_backup == null)
                            _backup = new CBackup(this.ItemBackupId);
                    }
                }
                return _backup;
            }
            set
            {
                _backup = value;
                _itemBackupId = null != value ? value.BackupId : int.MinValue;
            }
        }

        //Relationships - Collections (e.g. children)

        #endregion

        #region Properties - Customisation
        private static byte[] EMPTY = { };
        private static Guid HASH = CBinary.MD5_(EMPTY);
        public DataSet Dataset
        {
            get
            {
                var msSch = new MemoryStream(CBinary.Unzip(ItemSchemaXmlGz  ?? GetSchGz()));
                var msXml = new MemoryStream(CBinary.Unzip(ItemDatasetXmlGz ?? GetXmlGz()));

                var ds = new DataSet();
                ds.ReadXmlSchema(msSch);
                ds.ReadXml(msXml);
                ds.Tables[0].TableName = this.ItemTableName;
                return ds;
            }
            set
            {
                var ds = value;
                var dt = ds.Tables[0];
                var rc = dt.Rows.Count;

                this.ItemTableName = dt.TableName;
                this.ItemRowCount = rc;

                var xml = ds.GetXml();
                var xmlGz = CBinary.Zip(Encoding.Unicode.GetBytes(xml));
                this.ItemDatasetXmlGz = rc == 0 ? EMPTY : xmlGz;
                this.ItemDatasetMD5   = rc == 0 ? HASH :  CBinary.MD5_(xmlGz);

                var sch = ds.GetXmlSchema();
                var schGz = CBinary.Zip(Encoding.Unicode.GetBytes(sch));
                this.ItemSchemaXmlGz = rc == 0 ? EMPTY : schGz;
                this.ItemSchemaMD5   = rc == 0 ? HASH  : CBinary.MD5_(schGz);

            }
        }
        #endregion

        #region Save/Delete Overrides
        //Can Override base.Save/Delete (e.g. Cascade deletes, or insert related records)
        #endregion

        #region Custom Database Queries
        //For Stored Procs can use: MakeList (matching schema), or DataSrc.ExecuteDataset (reports etc)
        //For Dynamic sql, can use: SelectSum, SelectDistinct, SelectCount, SelectWhere (inherited methods)
        //                see also: SelectBy[FK], Search and Count (auto-generated sample queries)
        public CBackupItemList SelectByBackupIds(List<int> backupIds) { return SelectWhere(new CCriteriaList("ItemBackupId", ESign.IN, backupIds)); }

        public Dictionary<string, int> TableNameAndCount()
        {
            return DataSrc.MakeDictStrInt(string.Concat("SELECT DISTINCT ItemTableName, COUNT(*) FROM ", JOIN_BACKUP, " GROUP BY ItemTableName"));
        }
        public Dictionary<string, int> TableNameAndCount(int instanceId)
        {
            if (instanceId == int.MinValue)
                return TableNameAndCount(); 
            return DataSrc.MakeDictStrInt(string.Concat("SELECT DISTINCT ItemTableName, COUNT(*) FROM ", JOIN_BACKUP, " HAVING BackupInstanceId=", instanceId, " GROUP BY ItemTableName"));
        }
        public bool MoveTo(int backupId, int rowCount)
        {
            var d = new CNameValueList("ItemBackupId", backupId, "ItemRowCount", rowCount);
            var w = new CWhere(TABLE_NAME, new CCriteria("ItemId", ItemId), null);
            return DataSrc.Update(d, w) > 0;
        }
        public byte[] GetXmlGz()
        {
            var cmd = new CCommand("SELECT ItemDatasetXmlGz FROM " + TABLE_NAME + " WHERE ItemId=@itemId", new CNameValueList("itemId", ItemId), false);
            return (byte[])DataSrc.ExecuteScalar(cmd);
        }
        public byte[] GetSchGz()
        {
            var cmd = new CCommand("SELECT ItemSchemaXmlGz FROM " + TABLE_NAME + " WHERE ItemId=@itemId", new CNameValueList("itemId", ItemId), false);
            return (byte[])DataSrc.ExecuteScalar(cmd);
        }

        public long TotalSize(int backupId)
        {
            var pk = new CNameValueList("backupId", backupId);
            var sql = "SELECT ISNULL(SUM(Size),0) FROM " + VIEW_NAME + " WHERE ItemBackupId=@backupId";
            var x = DataSrc.ExecuteScalar(new CCommand(sql, pk, false));
            return Convert.ToInt64(x);
        }



        protected override CNameValueList ColumnNameValues()
        {
            CNameValueList data = new CNameValueList();
            data.Add("ItemId", CAdoData.NullVal(_itemId));
            data.Add("ItemBackupId", CAdoData.NullVal(_itemBackupId));
            data.Add("ItemTableName", CAdoData.NullVal(_itemTableName));
            data.Add("ItemRowCount", CAdoData.NullVal(_itemRowCount));

            if (null != _itemDatasetXmlGz)
                data.Add("ItemDatasetXmlGz", CAdoData.NullVal(_itemDatasetXmlGz));
            if (null != _itemSchemaXmlGz)
                data.Add("ItemSchemaXmlGz", CAdoData.NullVal(_itemSchemaXmlGz));

            data.Add("ItemDatasetMD5", CAdoData.NullVal(_itemDatasetMD5));
            data.Add("ItemSchemaMD5", CAdoData.NullVal(_itemSchemaMD5));
            return data;
        }
        #endregion

        #region Searching (Optional)
        //Dynamic search methods: (overload as required for common search patterns, cascade the BuildWhere overloads)
        //   Public  x5 - Simple, Paged, Transactional, Count, and Dataset
        //   Private x1 - BuildWhere
        //See also in-memory search options in list class, such as GetBy[FK] and Search

        //Simple
        public CBackupItemList SelectSearch(int instanceId, string table, string search /*, backupId, datasetMD5*/)  {   return SelectWhere(BuildWhere(instanceId, table, search));  } //, JOIN_OR_VIEW); }

        //Paged
        public CBackupItemList SelectSearch(CPagingInfo pi, int instanceId, string table, string search )
        {
            //pi.TableName = JOIN_OR_VIEW
            return SelectWhere(pi, BuildWhere(instanceId, table, search));
        }

        //Transactional
        public CBackupItemList SelectSearch(int instanceId, string table, string search,  IDbTransaction tx)   {   return SelectWhere(BuildWhere(instanceId, table, search), tx);   }   //, JOIN_OR_VIEW, tx); }

        //Dataset (e.g. ExportToCsv)
        public DataSet SelectSearch_Dataset(int instanceId, string table, string search )   {   return SelectWhere_Dataset(BuildWhere(instanceId, table, search));   }   //, JOIN_OR_VIEW); }

        //Count
        public int SelectCount(int instanceId, string table, string search )   {   return SelectCount(BuildWhere(instanceId, table, search));   }   //, JOIN_OR_VIEW); }

        //Filter Logic
        private CCriteriaList BuildWhere(int instanceId, string table, string search )
        {
            CCriteriaList where = new CCriteriaList();  //Defaults to AND logic
            

            //Simple search box UI
            if (!string.IsNullOrEmpty(search)) 
            {
                string wildCards = CAdoData.EscapeWildcardsForLIKE(search, CAdoData.EWildCards.Both);

                //Search a range of string columns
                CCriteriaGroup orExpr = new CCriteriaGroup(EBoolOperator.Or);
                orExpr.Add("ItemDescription", ESign.Like, wildCards);

                //Conclude
                if (orExpr.Group.Count > 0)
                    where.Add(orExpr);
            }

            //Other search Colums (customise as required)
            if (!string.IsNullOrEmpty(table)) where.Add("ItemTableName", table);
            if (int.MinValue != instanceId)   where.Add("BackupInstanceId", instanceId);

            return where;
        }
        #endregion


        #region Cloning
        public CBackupItem Clone(CDataSrc target, IDbTransaction txOrNull) //, int parentId)
        {
            //Shallow copy: Copies the immediate record, excluding autogenerated Pks
            CBackupItem copy = new CBackupItem(this, target);

            //Deep Copy - Child Entities: Cloned children must reference their cloned parent
            //copy.SampleParentId = parentId;

            copy.Save(txOrNull);

            //Deep Copy - Parent Entities: Cloned parents also clone their child collections
            //this.Children.Clone(target, txOrNull, copy.ItemId);

            return copy;
        }
        #endregion

        #region ToXml
        protected override void ToXml_Custom(System.Xml.XmlWriter w)
        {
            //Store(w, "Example", this..Example)
        }
        #endregion
    }
}